package connexionOracle;
import hebergement.LieuHebergement;
import java.sql.*;

import java.util.ArrayList;

import java.util.Vector;
import javax.swing.DefaultComboBoxModel;

import javax.swing.DefaultListModel;
import javax.swing.JComboBox;
import projection.Film;
import projection.Projection;

/**
 *
 * @author p0904552
 */
public class Requete {
    public static AccesBD acces;
    public static void createConnexion(){
        acces=AccesBD.getInstance();
    }
    public static ResultSet initHeberg() throws SQLException{
        ResultSet rs = acces.st.executeQuery("Select * from LIEUHEBERGEMENT");
        return rs;
    }

    public static ResultSet initProj() throws SQLException {
        ResultSet rs = acces.st.executeQuery("Select * from PROJECTION");
        return rs;
    }
    public static DefaultListModel getVip() throws SQLException {
         DefaultListModel listModelVip = new DefaultListModel();
         ResultSet rsVip = acces.st.executeQuery("SELECT  prenom || ' ' ||  nom FROM Vip");
         while(rsVip.next())
         {  
             listModelVip.addElement(rsVip.getString(1));
            }
         return listModelVip;
    }
    public static DefaultListModel getGroupeJury() throws SQLException
    {
         DefaultListModel listModelVip = new DefaultListModel();
         ResultSet rsVip = acces.st.executeQuery("SELECT DISTINCT GROUPEJURY FROM VIP WHERE ESTJURY = '1'");
         while(rsVip.next())
         {  
             listModelVip.addElement(rsVip.getString(1));
        }
         return listModelVip;
    }

    public static DefaultListModel getAttributVIP(String element, String groupe) throws SQLException
    {
        DefaultListModel listAttributVIP = new DefaultListModel();
         ResultSet rsVip = acces.st.executeQuery("SELECT prenom || ' ' ||  nom FROM "+element+" FROM VIP WHERE GROUPEJURY = '"+groupe+"'");
         while(rsVip.next())
         {
             listAttributVIP.addElement(rsVip.getString(1));
        }
        return listAttributVIP;
    }

    public static void addProj(Projection ea) throws SQLException{
        acces.st.executeQuery("Insert into PROJECTION VALUES("+ea.getIdProjection()+",\'"+ea.getFilm()+"\',"+ea.getSalle()+","+Date.valueOf(ea.getDate())+",\'"+ea.getVip()+"\')");
    }

    public static void updateEmp(Projection eg) throws SQLException {
        acces.st.executeQuery("UPDATE PROJECTION SET IDFILM = \'"+eg.getFilm()+"\', IDSALE ="+eg.getSalle()+", DATEPROJ ="+Date.valueOf(eg.getDate())+", VIP ="+eg.getVip()+"' WHERE IDPROJ ="+eg.getIdProjection());

    }

    public static void deleteEmp(Projection ef) throws SQLException {
        acces.st.executeQuery("Delete from PROJECTION where IDPROJ="+ef.getIdProjection());
    }
    public static Object getFilmInfos(String film,int wantedValue) throws SQLException {
        Film f = null;
        ResultSet rs = null;
        rs = acces.st.executeQuery("Select * from FILM where TITRE=\'"+film+"\'");
        rs.next();
            f =new Film(
                    rs.getInt("IDFILM"),
                    rs.getString("TITRE"),
                    rs.getString("GENRE"),
                    rs.getString("TYPEFILM"),
                    rs.getString("REALISATEUR"),
                    rs.getDate("ANNEE"),
                    rs.getInt("DUREEFILM"));
            
        switch(wantedValue){
            case 0:
                return f.getIdFilm();
            case 1:
                return f.getDureeFilm();
            default: return null;
        }    
    }
    public DefaultComboBoxModel getCBBNomFilm() throws SQLException
    {
        ResultSet rsFilmName = acces.st.executeQuery("Select titre from Film");
        DefaultComboBoxModel FilmNameCBB = new DefaultComboBoxModel();
        while(rsFilmName.next())
        {
            FilmNameCBB.addElement(rsFilmName.getString(1));
        }
        return FilmNameCBB;
    }


    public static void deleteHeb(LieuHebergement ef) throws SQLException {
        acces.st.executeQuery("Delete from LIEUHEBERGEMENT where IDLIEU="+ef.getIdLieu());
    }

    public static void updateHeb(LieuHebergement eg) throws SQLException {

        acces.st.executeQuery("UPDATE LIEUHEBERGEMENT SET TYPE = \'"+eg.getType()+"\', LIBELLELIEU =\'"+eg.getLibelleLieu()+"\', NBETOILES ="+eg.getNbEtoiles()+", NBPLACERESTANTE ="+eg.getNbPlaceRestante()+", POURJURY ="+eg.getPourJury()+", VIP =\'"+eg.getContient()+"\' WHERE IDLIEU ="+eg.getIdLieu());

    }

    public static void addHeb(LieuHebergement ea) throws SQLException {
        acces.st.executeQuery("Insert into LIEUHEBERGEMENT VALUES("+ea.getIdLieu()+",\'"+ea.getType()+"\',\'"+ea.getLibelleLieu()
                    +"\',"+ea.getNbEtoiles()+","+ea.getNbPlaceRestante()+","+ea.getPourJury()+",\'"+ea.getContient()+"\')");
    }

    public static ArrayList<String> getAllFilmName() throws SQLException{
        ArrayList<String> al = new ArrayList();
        ResultSet rs = acces.st.executeQuery("Select * from FILM");
        while(rs.next()){
            String s = rs.getString(2);
            al.add(s);
        }
        return al;
    }
    

    public String getTypeFilm(String titreFilm) throws SQLException
    {
        ResultSet rsFilmType = acces.st.executeQuery("Select typefilm from Film where titre = '"+titreFilm+"'");
        rsFilmType.next();
        return rsFilmType.getString(1);
    }
    public DefaultComboBoxModel getCBBNomSalle(String TypeSalle) throws SQLException
    {
        ResultSet rsSalleName = acces.st.executeQuery("Select NOMSALLE from Salle where typesalle like '%"+TypeSalle+"%'");
        DefaultComboBoxModel SalleNameCBB = new DefaultComboBoxModel();
        while(rsSalleName.next())
        {
            SalleNameCBB.addElement(rsSalleName.getString(1));
        }
        return SalleNameCBB;
    }
    public DefaultListModel getMembersGroupe(String groupe) throws SQLException
    {
         DefaultListModel listMembre = new DefaultListModel();
         ResultSet rsVip = acces.st.executeQuery("SELECT prenom || ' ' ||  nom FROM VIP WHERE GROUPEJURY = '"+groupe+"'");
         while(rsVip.next())
         {
             listMembre.addElement(rsVip.getString(1));
         }
        return listMembre;

   }
   }

